﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_Select_Comp]
	(
		@UserId NCHAR(10),
		@StartDate NCHAR(10),
		@EndDate NCHAR(10),
		@companyid VARCHAR(500)	
	)
AS
BEGIN
	
	DECLARE @strsql varchar(max)
	
	SET @strsql=';WITH list AS (
	SELECT companyid,companyname,sum(bd) AS bd,sum(wz) AS wz,sum(qt)+sum(tz) AS qt,sum(xfml) AS xfml,sum(xsml) AS xsml,
      sum(xse) AS xse,sum(xse)+isnull(sum(qqqdfqzcxse),0) AS xsze,sum(mlhj) AS mlhj,sum(mlhj)+isnull(sum(qqqdfqzcml),0) AS mlzhj,
      sum(xzys) AS xzys,sum(wksr) AS wksr,sum(tx) AS tx,sum(rcfy) AS rcfy,sum(cl) AS cl,
	  sum(sscl)+isnull((Select sum(amount) from Finance_Profit_Regulate 
				where CompanyId=fm.CompanyId and Convert(varchar(10),Regulatedate,120) 
				between '''+@StartDate+''' AND '''+@EndDate+''' and Deleted=0 and Approval=1),0) AS sscl,
	  sum(zxf) AS zxf,
      SUM(qyqq) AS qyqq,ISNULL(SUM(QYQQ_CustomerCount),0) AS QYQQ_CustomerCount,SUM(tz) AS tz,SUM(gdzc) AS gdzc,SUM(albb) AS albb,sum(yggz) as yggz,sum(ygbx) as ygbx,sum(sf) as sf,
      sum(ff) as ff,sum(zbfy) as zbfy,sum(ygfl) as ygfl,sum(sjglf) as sjglf,sum(qtzc) as qtzc,sum(fyze) as fyze,sum(fh) as fh,
      sum(ssclwsr) as ssclwsr,sum(fyyxsr) as fyyxsr,isnull(sum(xfxse),0) as xfxse,isnull(sum(xzxse),0) as xzxse,isnull(sum(bd_je),0) as bd_je,
      isnull(sum(wz_je),0) as wz_je,isnull(sum(qyQQ_je),0) as qyQQ_je,isnull(sum(ssclwzc),0) as ssclwzc,isnull(sum(jkzc),0) as jkzc,
      isnull(sum(jksr),0) as jksr,isnull(sum(dkzc),0) as dkzc,isnull(sum(dksr),0) as dksr,isnull(sum(tzzc),0) as tzzc,isnull(sum(tzsr),0) as tzsr,
      isnull(sum(idc),0) as idc,isnull(sum(idc_je),0) as idc_je,isnull(sum(qt_je),0) as qt_je,isnull(sum(jj),0) as jj,isnull(sum(bcfk),0) as bcfk,
	  isnull(sum(kqfk),0) as kqfk,isnull(sum(qqqdfqzcml),0) as qqqdfqzcml,isnull(sum(qqqdfqzcxse),0) as qqqdfqzcxse,isnull(sum(bdztc),0) as bdztc,
	  isnull(sum(bdztcxse),0) as bdztcxse,isnull(sum(dhkdf),0) as dhkdf,isnull(sum(sdf),0) as sdf,isnull(sum(bgfy),0) as bgfy,isnull(sum(clf),0) as clf,
	  isnull(sum(zdf),0) as zdf,isnull(sum(cwfy),0) as cwfy,isnull(sum(zppxf),0) as zppxf,isnull(sum(scxcf),0) as scxcf, 
	  isnull((Select sum(amount) from Finance_Profit_Regulate 
				where CompanyId=fm.CompanyId and Convert(varchar(10),Regulatedate,120) 
				between '''+@StartDate+''' AND '''+@EndDate+''' and Deleted=0 and Approval=1),0) sscltz
	  FROM FinanceMonthTable fm WHERE 
      tmonth BETWEEN '''+@StartDate+''' AND '''+@EndDate+''' and companyid IN ('+@companyid+')
     GROUP BY companyid,companyname )
    SELECT * FROM list,(SELECT SUM(bd) AS bd1,
      SUM([wz]) AS wz1,SUM([qt]) AS qt1,SUM([xfml]) AS xfml1,SUM([xsml]) AS xsml1,SUM([xse]) AS xse1,SUM([xsze]) AS xsze1,SUM([mlhj]) AS mlhj1,
	  SUM([mlzhj]) AS mlzhj1,SUM([xzys]) AS xzys1,SUM([wksr]) AS wksr1,SUM([tx]) as tx1,SUM([rcfy]) AS rcfy1,SUM([cl]) AS cl1,SUM([sscl]) AS sscl1,
      SUM([zxf]) as zxf1,SUM(qyqq) AS qyqq1,SUM(QYQQ_CustomerCount) AS QYQQ_CustomerCount1,SUM(tz) AS tz1,SUM(gdzc) AS gdzc1,sum(albb) AS albb1,sum(yggz) as yggz1,sum(ygbx) as ygbx1,sum(sf) as sf1,
      sum(ff) as ff1,sum(zbfy) as zbfy1,sum(ygfl) as ygfl1,sum(sjglf) as sjglf1,sum(qtzc) as qtzc1,sum(fyze) as fyze1,sum(fh) as fh1,sum(ssclwsr) as ssclwsr1,
	  sum(fyyxsr) as fyyxsr1,sum(xfxse) as xfxse1,sum(xzxse) as xzxse1,sum(bd_je) as bd_je1,sum(wz_je) as wz_je1,sum(qyQQ_je) as qyQQ_je1,sum(ssclwzc) as ssclwzc1,
      sum(jkzc) as jkzc1,sum(jksr) as jksr1,sum(dkzc) as dkzc1,sum(dksr) as dksr1,sum(tzzc) as tzzc1,sum(tzsr) as tzsr1,sum(idc) as idc1,sum(idc_je) as idc_je1,
      sum(qt_je) as qt_je1,SUM(jj) as jj1,SUM(bcfk) as bcfk1,SUM(kqfk) as kqfk1,SUM(qqqdfqzcml) as qqqdfqzcml1,SUM(qqqdfqzcxse) as qqqdfqzcxse1,sum(bdztc) bdztc1,
	  sum(bdztcxse) bdztcxse1,sum(dhkdf) as dhkdf1,sum(sdf) as sdf1,sum(bgfy) as bgfy1,sum(clf) as clf1,sum(zdf) as zdf1,sum(cwfy) as cwfy1,sum(zppxf) as zppxf1,
	  sum(scxcf) as scxcf1,sum(sscltz) sscltz1
    FROM list ) AS n
      ORDER BY companyid'
       
       EXEC(@strsql)
	   --return @strsql
END